package com.hanma.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.hanma.util.DBUtil;
import com.hanma.bean.User;
import com.hanma.dao.UserDao;

public class UserDaoImpl implements UserDao{
//	public static void main(String[] args) {
//		UserDaoImpl ud = new UserDaoImpl();
//		User u = new User("su","12345",1,18,"123456");
//		ud.insert(u);
//	}

	@Override
	public int insert(User user) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql="insert into user(username,password,con_password,head)  values(?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getCon_password());
			ps.setString(4, user.getHead());
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public int update(User user,Integer id) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql="update user set nickname=?,tel=?,email=?,password=? where id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getNickname());
			ps.setString(2, user.getTel());
			ps.setString(3, user.getEmail());
			ps.setString(4, user.getPassword());
			ps.setInt(5, id);
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public int updateHead(Integer id,String head) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql="update user set head=? where id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, head);
			ps.setInt(2, id);
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}

	@Override
	public int delete(Integer id) {
		Connection conn = DBUtil.open();
		int num=0;
		String sql="update user set status=0,is_del=1 where id=?"; 
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			num=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(null, ps, conn);
		}
		return num;
	}
//	@Override
//	public int delete(Integer id) {
//		Connection conn = DBUtil.open();
//		int num=0;
//		String sql="delete from user where id=?"; 
//		PreparedStatement ps = null;
//		try {
//			ps = conn.prepareStatement(sql);
//			ps.setInt(1, id);
//			num=ps.executeUpdate();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}finally {
//			DBUtil.close(null, ps, conn);
//		}
//		return num;
//	}

	@Override
	public User find(String name,String password) {
		Connection conn = DBUtil.open();
		String sql="select * from user where (username=? or tel=? or email=?) and password=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, name);
			ps.setString(3, name);
			ps.setString(4, password);
			rs = ps.executeQuery();
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String username=rs.getString("username");
				User user = new User(id,username,password);
				return user;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return null;
	}
	public boolean findByName(String username) {
		Connection conn = DBUtil.open();
		String sql="select * from user where username=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Boolean flag=false;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			while(rs.next()) {
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return flag;
	}
	public User findById(Integer id) {
		Connection conn = DBUtil.open();
		String sql="select * from user where id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				String username=rs.getString("username");
				String nickname=rs.getString("nickname");
				String tel=rs.getString("tel");
				String email=rs.getString("email");
				String password=rs.getString("password");
				String head=rs.getString("head");
				User user = new User(id,username,nickname,password,tel,email,head);
				return user;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return null;
	}

	@Override
	public List<User> findAll() {
		Connection conn = DBUtil.open();
		String sql="select * from user where is_del = 0 order by id"; 
		Statement str = null;
		ResultSet rs = null;
		List<User> userList = new ArrayList<>();
		try {
			str = conn.createStatement();
		    rs = str.executeQuery(sql);
			
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String username=rs.getString("username");
				String nickname=rs.getString("nickname");
				String password=rs.getString("password");
				String tel=rs.getString("tel");
				String email=rs.getString("email");
				Integer status=rs.getInt("status");
				Integer is_del=rs.getInt("is_del");
				String head=rs.getString("head");
				User user = new User(id,username,nickname,password,tel,email,head);
				userList.add(user);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, str, conn);
		}
		return userList;
	}

	@Override
	public List<User> findAll(Integer start, Integer size) {
		Connection conn = DBUtil.open();
		String sql="select * from user where is_del = 0 order by id limit ?,?"; 
		List<User> userList = new ArrayList<>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, size);
		    rs = ps.executeQuery();
			while(rs.next()) {
				Integer id=rs.getInt("id");
				String username=rs.getString("username");
				String nickname=rs.getString("nickname");
				String password=rs.getString("password");
				String tel=rs.getString("tel");
				String email=rs.getString("email");
				Integer status=rs.getInt("status");
				Integer is_del=rs.getInt("is_del");
				String head=rs.getString("head");
				User user = new User(id,username,nickname,password,tel,email,head);
				userList.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return userList;
	}
	

}
